# -*- coding: utf-8 -*-
import time, MySQLdb

class DBOutputer(object):
    def __init__(self):
        self.conn = MySQLdb.connect(host="localhost",user="wolf",passwd="wolf_123456",db="wolf",charset="utf8")
        self.datas = []

    def collect_data(self, data):
        if data is None:
            return
        self.datas.append(data)

    def output_mysql(self):
        cursor =self.conn.cursor()
        for data in self.datas:
            if self.__search_data(data,cursor):
                self.__updata_data(data,cursor)
            else:
                self.__save_newDate(data,cursor)
        cursor.close()
        self.conn.commit()
        self.conn.close()
    # 检查是否存在
    def __search_data(self,data,cursor):
        sql = "select * FROM spider_program where id = " +data['id']
        count = cursor.execute(sql)
        if count > 0:
            return True
        else:
            return False
    # 插入新的数据
    def __save_newDate(self,data,cursor):
        sql = "insert into spider_program ( "
        values = "( "
        param = []
        for key ,value in data.iteritems():
            sql = sql+key+","
            values += "%s,"
            param.append(value)
        values = values[:-1]
        sql = sql[:-1]
        values += " )"
        sql = sql + ") values "+ values
        print "log-->"+sql
        cursor.execute(sql,param)
    # 更新数据
    def __updata_data(self,data,cursor):
        sql = "update spider_program SET "
        param = []
        for key, value in data.iteritems():
            if (key != 'id') and (key != 'doubanId') and (key != 'sid') and (key != 'crawlTime') and (key != 'information'):
                sql += key+"=%s,"
                param.append(value)
        sql =sql[:-1]
        sql += " where id = "+ data['id']
        print "log-->"+sql
        cursor.execute(sql,param)

# #删除表
# sql = "drop table if exists user"
# cursor.execute(sql)
#
# #创建
# sql = "create table if not exists user(name varchar(128) primary key, created int(10))"
# cursor.execute(sql)
#
# #写入
# sql = "insert into user(name,created) values(%s,%s)"
# param = ("aaa",int(time.time()))
# n = cursor.execute(sql,param)
# print 'insert',n
#
# #写入多行
# sql = "insert into user(name,created) values(%s,%s)"
# param = (("bbb",int(time.time())), ("ccc",33), ("ddd",44) )
# n = cursor.executemany(sql,param)
# print 'insertmany',n
#
# #更新
# sql = "update user set name=%s where name='aaa'"
# param = ("zzz")
# n = cursor.execute(sql,param)
# print 'update',n
#
# #查询
# n = cursor.execute("select * from user")
# for row in cursor.fetchall():
#     print row
#     for r in row:
#         print r
#
# #删除
# sql = "delete from user where name=%s"
# param =("bbb")
# n = cursor.execute(sql,param)
# print 'delete',n
#
# #查询
# n = cursor.execute("select * from user")
# print cursor.fetchall()
#
# cursor.close()
#
# #提交
# conn.commit()
# #关闭
# conn.close()
